package org.jeecg.modules.htxincailiao.analytics.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.jeecg.modules.htxincailiao.analytics.domain.vo.HTBackendVisitDistributionVO;
import org.jeecg.modules.htxincailiao.analytics.entity.HTBackendVisitAction;

import java.util.List;

/**
 * @Description: ht_backend_visit_action
 * @Author: jeecg-boot
 * @Date: 2022-05-27 10:17:45
 * @Version: V1.0
 */
public interface HTBackendVisitActionMapper extends BaseMapper<HTBackendVisitAction> {

    @Select("<script>" +
            " SELECT" +
            " T.shop_id," +
            " T.shop_pv_count," +
            " T.shop_uv_count," +
            " T.shop_total_browse_time," +
            " CONVERT (" +
            " T.shop_total_browse_time / T.shop_pv_count," +
            " DECIMAL ( 15, 2 )) AS shop_average_browse_time," +
            " s.name AS shop_name " +
            " FROM" +
            " (" +
            " SELECT" +
            " shop_id," +
            " COUNT( visit_ip ) AS shop_pv_count," +
            " COUNT( DISTINCT (visit_ip) ) AS shop_uv_count," +
            " SUM(" +
            " TIMESTAMPDIFF( SECOND, visit_time, leave_time )) AS shop_total_browse_time " +
            " FROM" +
            " ht_backend_visit_action " +
            " WHERE" +
            " <![CDATA[ visit_time > #{beginTime} ]]> " +
            " AND <![CDATA[ visit_time < #{endTime} ]]> " +
            " GROUP BY" +
            " shop_id " +
            " ) AS T" +
            " LEFT JOIN ht_shop s ON s.id = T.shop_id  " +
            "</script>")
    List<HTBackendVisitDistributionVO> queryDistribution(@Param("beginTime") String beginTime,
                                                         @Param("endTime") String endTime);
}
